* Set file directory using cd

import excel "Grid Africa\Files\grid_nearUSGS.xlsx", sheet("nearUSGS") firstrow case(lower) clear

gen minesusgs1900_20km=(near_dist<20000 & mines1900>=1)
gen minesusgs1914_20km=(near_dist<20000 & mines1914>=1)
gen minesusgs1924_20km=(near_dist<20000 & mines1924>=1)

rename mines1900 minesusgs1900_50km
rename mines1914 minesusgs1914_50km
rename mines1924 minesusgs1924_50km

collapse (sum)	minesusgs1900_50km  minesusgs1914_50km minesusgs1924_50km minesusgs1900_20km minesusgs1914_20km minesusgs1924_20km longitude latitude, by(gridcell)

tempfile usgs
save "`usgs'"


import excel "Grid Africa\Files\grid_nearSNL.xlsx", sheet("nearSNL") firstrow case(lower) clear
 
rename descriptio gridcell

gen minessnl1900_20km=(near_dist<20000 & mines1900>=1)
gen minessnl1914_20km=(near_dist<20000 & mines1914>=1)
gen minessnl1924_20km=(near_dist<20000 & mines1924>=1)

rename mines1900 minessnl1900_50km
rename mines1914 minessnl1914_50km
rename mines1924 minessnl1924_50km

drop if startup3>1924

collapse (sum)	minessnl1900_50km  minessnl1914_50km minessnl1924_50km minessnl1900_20km minessnl1914_20km minessnl1924_20km, by(gridcell)

merge 1:1 gridcell using `usgs'
drop _merge

** Replace missing values
foreach i of varlist minesusgs1900_50km  minesusgs1914_50km minesusgs1924_50km minesusgs1900_20km minesusgs1914_20km minesusgs1924_20km minessnl1900_50km  minessnl1914_50km minessnl1924_50km minessnl1900_20km minessnl1914_20km minessnl1924_20km {
replace  `i'=0 if `i'==.
}

*************************************
** Pooling mines information
gen mines1924_50km=(minesusgs1924_50km+minessnl1924_50km>=1)
gen mines1914_50km=(minesusgs1914_50km+minessnl1914_50km>=1)
gen mines1900_50km=(minesusgs1900_50km+minessnl1900_50km>=1)
label var mines1924_50km "Grid has at least one mine in 50km radius in 1924"
label var mines1914_50km "Grid has at least one mine in 50km radius in 1914"
label var mines1900_50km "Grid has at least one mine in 50km radius in 1900"


gen mines1924_20km=(minesusgs1924_20km+minessnl1924_20km>=1)
gen mines1914_20km=(minesusgs1914_20km+minessnl1914_20km>=1)
gen mines1900_20km=(minesusgs1900_20km+minessnl1900_20km>=1)
label var mines1924_20km "Grid has at least one mine in 20km radius in 1924"
label var mines1914_20km "Grid has at least one mine in 20km radius in 1914"
label var mines1900_20km "Grid has at least one mine in 20km radius in 1900"


egen n_mines1924_50km=rowmax(minesusgs1924_50km minessnl1924_50km)
egen n_mines1900_50km=rowmax(minesusgs1900_50km minessnl1900_50km)

egen n_mines1924_20km=rowmax(minesusgs1924_20km minessnl1924_20km)
egen n_mines1900_20km=rowmax(minesusgs1900_20km minessnl1900_20km)

label var n_mines1924_50km "Number of mines as max(USGS, SNL) in 1924 in 50km range of grid"
label var n_mines1900_50km "Number of mines as max(USGS, SNL) in 1900 in 50km range of grid"

label var n_mines1924_20km "Number of mines as max(USGS, SNL) in 1924 in 20km range of grid"
label var n_mines1900_20km "Number of mines as max(USGS, SNL) in 1900 in 20km range of grid"

keep gridcell n_mines* mines*

drop minesusgs1924_50km minesusgs1924_20km minesusgs1914_50km minesusgs1914_20km minesusgs1900_50km minesusgs1900_20km minessnl1924_50km minessnl1924_20km minessnl1914_50km minessnl1914_20km minessnl1900_50km minessnl1900_20km
save "Grid Africa\Files\grid_mining.dta", replace

